import pymysql


# 定义数据库工具类
class DBTools(object):
    # 定义创建连接方法
    @staticmethod
    def create_conn():
# 指定host\user\port\password\charsedatabase\返回conn
        conn = pymysql.connect(host="192.168.43.115", port=3306, user="root", password="root0219..", database="zshj-auth",
                           charset="utf8")

        return conn
    # 定义查询一条方法
    @staticmethod
    def query_fetchone(sql):
        my_conn=None
        my_cursor=None
        res=None
        try:
            # 获取游标
            my_conn=DBTools.create_conn()
            my_cursor=my_conn.cursor()
            # 执行sql
            my_cursor.execute(sql)
            # 查询数据库，进行结果集提取，返回一条数据
            res=my_cursor.fetchone()
            return res
        except Exception as err:
            print(err)

        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()

    # 定义查询指定条方法
    @staticmethod
    def query_fetchmany(sql,n):
        my_conn = None
        my_cursor = None
        res = None
        try:
            # 获取游标
            my_conn = DBTools.create_conn()
            my_cursor = my_conn.cursor()
            # 执行sql
            my_cursor.execute(sql)
            # 查询数据库，进行结果集提取，返回一条数据
            res = my_cursor.fetchmany(n)
            return res
        except Exception as err:
            print(err)

        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
    # 定义查询全部条方法
    @staticmethod
    def query_fetchall(sql):
        my_conn = None
        my_cursor = None
        res = None
        try:
            # 获取游标
            my_conn = DBTools.create_conn()
            my_cursor = my_conn.cursor()
            # 执行sql
            my_cursor.execute(sql)
            # 查询数据库，进行结果集提取，返回一条数据
            res = my_cursor.fetchall()
            return res
        except Exception as err:
            print(err)

        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
    # 定义增删改方法
    @staticmethod
    def idu_method(sql):
        my_cursor = None
        my_conn = None
        res=None
        try:
            # 获取游标
            my_conn = DBTools.create_conn()
            my_cursor=my_conn.cursor()
            # 执行sql
            my_cursor.execute(sql)


            # 提交事务
            my_conn.commit()
            print("Affected rows:", my_conn.affected_rows())
        except Exception as er:

            # 事务回滚
            my_conn.rollback()
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()

if __name__ == '__main__':
    res=DBTools.query_fetchone("select * from sys_role;")
    # print(res)
    # res1=DBTools.query_fetchmany("select * from t_book",2)
    # print(res1)
    # res2=DBTools.query_fetchall("select * from t_book")
    # print(res2)
    # DBTools.idu_method("insert into t_books (id,title,pub_date) values (7,'狂飙','2024-4-23')")





